package com.mskj.p3.dao.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.mskj.p3.dao.HelpDao;
import com.mskj.p3.model.Help;
import com.mskj.p3.model.HelpSet;
import com.mskj.p3.model.PageBean;
import com.mskj.p3.model.User;
import com.mskj.p3.util.SQLPageUtil;

@Repository
public class HelpDaoImp implements HelpDao {
	
	@Resource
	private JdbcTemplate jt;
	
	@Override
	public boolean postHelp(Help h) {
		String sql = "insert into tbl_help values (seq_help.nextval,?,sysdate,?,?)";
		Object[] params = new Object[]{h.getHelpinfo(),h.getHid(),h.getUid()};
		int updateRecordNum = jt.update(sql,params);
		return updateRecordNum == 1;
	}

	@Override
	public boolean deleteHelp(Help h, User u) {
		String sql = "delete from tbl_help where helpid = ? and u_id =?";
		Object[] params = new Object[]{h.getHelpid(),u.getUid()};
		int updateRecordNum = jt.update(sql,params);
		return updateRecordNum == 1;
	}

	@Override
	public List<HelpSet> queryHelpSet(User user,PageBean pb) {
		String sql = "select helpid,helpinfo,to_char(helptime,'yyyy/mm/dd hh24:mi:ss') helptime,hid,u.u_id,uname,purl from tbl_help h,tbl_user u,tbl_picture p where h.u_id=u.u_id and u.pid = p.pid and hid in (select hid from tbl_choose c,tbl_user u where u.u_id=c.u_id and u.u_id=?) order by helptime desc";
		Object[] params = new Object[]{user.getUid()};
//		sql = SQLPageUtil.getPageSql(sql, pb);
		List<HelpSet> helpsetlist = (List<HelpSet>)jt.query(sql,params,new HelpSetBeanMapper());
		return helpsetlist;
	}

	private class HelpSetBeanMapper implements RowMapper<HelpSet>{
		
		@Override
		public HelpSet mapRow(ResultSet rs, int pos) throws SQLException {
			HelpSet hs = new HelpSet();
			hs.setHelpid(rs.getString("helpid"));
			hs.setHelpinfo(rs.getString("helpinfo"));
			hs.setHelptime(rs.getString("helptime"));
			hs.setHid(rs.getString("hid"));
			hs.setUid(rs.getString("u_id"));
			hs.setUname(rs.getString("uname"));
			hs.setPurl(rs.getString("purl"));
			return hs;
		}
		
	}

	@Override
	public List<HelpSet> queryMyHelpSet(User user, PageBean pb) {
		String sql = "select helpid,helpinfo,to_char(helptime,'yyyy/mm/dd hh24:mi:ss') helptime,hid,u.u_id,uname,purl from tbl_help h,tbl_user u,tbl_picture p where h.u_id = u.u_id and p.pid = u.pid and u.u_id = ?";
		Object[] params = new Object[]{user.getUid()};
//		sql = SQLPageUtil.getPageSql(sql, pb);
		List<HelpSet> helpsetlist = (List<HelpSet>)jt.query(sql,params,new HelpSetBeanMapper());
		return helpsetlist;
	}

}
